import 'package:shared_preferences/shared_preferences.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'user_table.dart';
import 'msg_table.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper.internal();

  factory DatabaseHelper() => _instance;

  static Database _db;
  String uid = '';

  Future<Database> get db async {
    SharedPreferences spfs = await SharedPreferences.getInstance();
    String id = spfs.getInt('uid').toString();
    if (_db != null) {
      if (uid == id) {
        return _db;
      }else{
        _db = await initDb();
        return _db;
      }
    }
    _db = await initDb();
    return _db;
  }

  DatabaseHelper.internal();

  initDb() async {
    SharedPreferences spfs = await SharedPreferences.getInstance();
    uid = spfs.getInt('uid').toString();
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'newchat$uid.db');
    var ourDb = await openDatabase(path, version: 1, onCreate: _onCreate);
    return ourDb;
  }

  //创建数据库表
  void _onCreate(Database db, int version) async {
    //初始化user
    await db.execute(
        "create table User$uid(uid integer primary key,nickname text not null ,icon text not null,"
            "country text not null,code text not null,birth text,times text not null,uptime text,vip integer not null,price integer not null)");
    //初始化msg
    await db.execute(
        "create table Msg$uid(id integer primary key,uid integer,times integer not null,"
            "state integer not null,content text,video text,img text,type integer not null )");
  }


  Future<List> getTotalList() async {
    var dbClient = await db;
    var result = await dbClient.rawQuery("SELECT * FROM User$uid ");
    return result.toList();
  }

  ///根据Uid排序获取消息
  Future<List> getNewMsg(int id) async {
    var dbClient = await db;
    var result = await dbClient.rawQuery("SELECT * FROM Msg$uid where uid = $id order by times");
    return result.toList();
  }

  ///插入msg
  Future<int> saveItemMsg(Msg msg) async {
    var dbClient = await db;
    int res = await dbClient.insert("Msg$uid", msg.toMap());
    return res;
  }

  ///按照id查询user
  Future<UserTable> getItem(int id) async {
    var dbClient = await db;
    var result = await dbClient.rawQuery("SELECT * FROM User$uid WHERE uid = $id");
    if (result.length>0){
      UserTable user = UserTable.fromMap(result.first);
      return user;
    }
    return null;
  }

  ///更新数据库用户
  Future<void> updateUser(UserTable user) async{
    var dbClient = await db;
    return await dbClient.update("User$uid", user.toMap(),
        where: "uid = ?", whereArgs: [user.uid]);
  }

  ///插入user
  Future<int> saveItemUser(UserTable user) async {
    var dbClient = await db;
    int res = await dbClient.insert("User$uid", user.toMap());
    return res;
  }

  ///查询Uid时间j降序
  Future<List> getNewUid() async {
    var dbClient = await db;
    var result = await dbClient.rawQuery("SELECT t1.* from Msg$uid t1 inner join (SELECT uid, max(times) as times from Msg$uid GROUP BY uid) t2 on t1.uid=t2.uid and t1.times=t2.times order by times desc");
    return result.toList();
  }

  ///查询state未读总数
  Future<int> getCountState(int id) async {
    var dbClient = await db;
    return Sqflite.firstIntValue(await dbClient.rawQuery(
        "SELECT COUNT(*) FROM Msg$uid where uid = $id and state = 1"
    ));
  }

  ///修改id对应的已读状态
  Future<int> updateTable(int id) async {
    var dbClient = await db;
    return await dbClient.rawUpdate('update Msg$uid Set state = 0 where uid = $id');
  }

  }